DECLARE @INTMONTH INT = 1
DECLARE @NOWMONTH INT = MONTH(CONVERT(DATE,(SELECT [MAX_DATE_ENDMONTH] FROM [DATE_EM_REPORT])))
DECLARE @SMONTH NVARCHAR(20)
DECLARE @SQL NVARCHAR(MAX)

DELETE FROM NFI_TOI_ACCOUNT_PLANNING

SET @SMONTH= CAST(@NOWMONTH AS NVARCHAR(20))

	if(@NOWMONTH <10) set @SMONTH = '0' + @SMONTH
EXEC('
	IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE o.xtype IN (''U'') AND o.name = ''NFI_TOI_ACCOUNT_PLANNING_2016'+@SMONTH+''')
        DROP TABLE NFI_TOI_ACCOUNT_PLANNING_2016'+@SMONTH+'
	
	SELECT * INTO [NFI_TOI_ACCOUNT_PLANNING_2016'+@SMONTH+']
	FROM [TRANGDTT18].[SME2016].[DBO].[NFI_TOI_2016'+@SMONTH+'] ')
	--select * from NFI_TOI_ACCOUNT_PLANNING_201605
	--select * from NFI_TOI_ACCOUNT_PLANNING_201608


WHILE @INTMONTH <= @NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))
	
	if(@INTMONTH <10) set @SMONTH = '0' + @SMONTH

	EXEC('
	INSERT INTO NFI_TOI_ACCOUNT_PLANNING
	SELECT SUM(CONVERT(FLOAT, AMT)) AS AMT_QD, [TYPE], CIF , YEARMONTH='+@SMONTH+'
	FROM [NFI_TOI_ACCOUNT_PLANNING_2016'+@SMONTH+'] 
	WHERE CIF IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM TBL_ACCOUNT_PLANNING)
	GROUP BY CIF, [TYPE]
	ORDER BY CIF')
	--EXEC(@SQL)
	SET @INTMONTH = @INTMONTH + 1
END

DELETE FROM NFI_TOI_ACCOUNT_PLANNING_SUM

INSERT INTO NFI_TOI_ACCOUNT_PLANNING_SUM
SELECT *,0 
FROM 
(SELECT AMT_QD, [TYPE], CIF, YEARMONTH
FROM NFI_TOI_ACCOUNT_PLANNING ) AS A
PIVOT
(MAX(AMT_QD) FOR TYPE IN (FEX,NFI,TOI)) AS PVT

UPDATE NFI_TOI_ACCOUNT_PLANNING_SUM SET FEX = 0 WHERE FEX IS NULL
UPDATE NFI_TOI_ACCOUNT_PLANNING_SUM SET NFI = 0 WHERE NFI IS NULL

UPDATE NFI_TOI_ACCOUNT_PLANNING_SUM SET NII= TOI - FEX - NFI
